<?php
require_once 'Table.php';

/**
 * La f�brica de las tablas para comunicarse con la base de datos
 */
class ConnectionDB{

	//----------------------------------------------------------------------------
	//Constantes de Conexi�n de la BD
	//----------------------------------------------------------------------------
	const DATABASE_NAME = "clientes";
	const SERVER = "localhost";
	const USER = "root";
	const PASSWORD = "vertrigo";

	//----------------------------------------------------------------------------
	//Constantes de Valores para los parametros
	//----------------------------------------------------------------------------
	const INT = "INT";
	const DOUBLE = "DOUBLE";
	const CHAR = "CHAR";
	const STRING_NAME = "VARCHAR(32)";
	const STRING_COMMENT = "VARCHAR(300)";
	const STRING_LONG_COMMENT = "VARCHAR(500)";
	const STRING_BLOG = "VARCHAR(1000)";
	const DATE = "DATE";

	const NOT_NULL = "NOT NULL";
	const NNULL = "NULL";

	//----------------------------------------------------------------------------
	//Constantes de los tipos de indices
	//----------------------------------------------------------------------------
	const INDEX_KEY = "INDEX";
	const PRIMARY_KEY = "PRIMARY KEY";
	const NONE_KEY = "NONE KEY";
	
	//----------------------------------------------------------------------------
	//Constantes de valores por defecto
	//----------------------------------------------------------------------------
	const DEFAULT_VALUE = "DEFAULT ''";
	const NO_DEFAULT_VALUE = "NO_DEFAULT_VALUE";

	//----------------------------------------------------------------------------
	//Atributos
	//----------------------------------------------------------------------------

	/**
	 * El nombre del usuario
	 */
	private $user_name;

	/**
	 * La contrase�a del usuario
	 */
	private $user_pass;

	/**
	 * Conexi��n con la BD
	 */
	private $link;

	//----------------------------------------------------------------------------
	//Funci�n Constructor
	//----------------------------------------------------------------------------

	public function __construct($user_name = "", $user_pass = ""){
        $this->user_name = ConnectionDB::USER;
        $this->user_pass = ConnectionDB::PASSWORD;
	}

	//----------------------------------------------------------------------------
	//Funciones Getters y Setters
	//----------------------------------------------------------------------------

	/**
	 * Retorna el nombre de usuario
	 */
	public function get_user_name(){
		return $this->user_name;
	}

	/**
	 * Retorna la contrase�a del usuario
	 */
	public function get_user_pass(){
		return $this->user_pass;
	}

	//----------------------------------------------------------------------------
	//Funciones para las tablas
	//----------------------------------------------------------------------------

	/**
	 * Valida si existe una tabla con el nombre y los parámetros dados, en caso de no ser asi, elimina la existente y crea una nueva
	 */
	public function get_table($table_name, $table_params){
		if($this->exist_table($table_name)){
			if(!$this->valids_fields($table_name, $table_params)){
				$this->delete_table($table_name);
				$this->create_table($table_name,$table_params);
			}
		}
		else{
			$this->create_table($table_name,$table_params);
		}
		
		$table = new Table($table_name, $this);
		return $table;
	}

	/**
	 * Valida si la tabla es correcta y existe con ese nombre y parámetros
	 */
	public function valids_fields($table_name, $table_params){
		$query = "SHOW COLUMNS FROM ".$table_name;
		
		$list = $this->get_query($query);

		$valid_table = TRUE;

		$num_fields = 0;
		$num_param = 0;
		foreach ($list as $i => $value) {
			$valid_field = FALSE;
			$nump = 0;
			foreach ($table_params as $j => $value) {
				if($list[$i][0]==$table_params[$j][0]){
					$valid_field = TRUE;
				}
				$nump++;
			}
			if(!$valid_field){
				$valid_table = FALSE;
			}
			$num_param = $nump;
			$num_fields++;
		}

		//Agrega las columnas que faltan
		if($valid_table && $num_fields<$num_param){
			foreach ($table_params as $i => $value) {
				$add = FALSE;
				foreach ($list as $j => $value) {
					if($table_params[$i][0]==$list[$j]){
						$add = TRUE;
					}
				}
				if(!$add){
					$this->add_column($table_name,$table_params[$i]);
				}
			}
		}
		return $valid_table;
	}

	/**
	 * Valida si la tabla existe
	 */
	function exist_table($table_name){
		$query = "SHOW TABLES FROM ".ConnectionDB::DATABASE_NAME;

		$list = $this->get_query($query);

		$valid_name = FALSE;
		foreach ($list as $i => $value) {
			if($list[$i][0] == $table_name){
				$valid_name = TRUE;
			}
		}
		return $valid_name;
	}

	/**
	 * Elimina una tabla dado el nombre
	 */
	private function delete_table($table_name){
		$query = "DROP TABLE ".$table_name;
		$this->get_query($query);
	}

	/**
	 * Crea una tabla dado el nombre y las caracteristicas de las columnas
	 */
	private function create_table($table_name, $table_params){
		$query = "CREATE TABLE ".$table_name." (";
		$index = array();
		$i_index  = 0;

		foreach($table_params as $i => $value){
			$name = $table_params[$i][0];
			$type = $table_params[$i][1];
			$is_not_null = ($table_params[$i][2] == ConnectionDB::NOT_NULL);
			$key = $table_params[$i][3];
			$default = ($table_params[$i][4] == ConnectionDB::DEFAULT_VALUE);
			
			//Los valores
			if($name == "id"){
				$query .= " id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ";
			}else{
				$query .= ", ".$name." ".$type." ";
				if($is_not_null){
					$query .= ConnectionDB::NOT_NULL." ";
				}
                $numeric = ($type == ConnectionDB::INT || $type == ConnectionDB::DOUBLE);
                if($default && $numeric){
					$query .= "DEFAULT 0 ";
				}else if($default && !$numeric){
					$query .= "DEFAULT '' ";
				}
			}
			//si es indice
			if($key == ConnectionDB::INDEX_KEY){
				$index[] = $name;
				$i_index++;
			}
		}

		//Agrega los indices
		if($i_index>0){
			$query .= ", INDEX (";
			foreach($index as $i => $val){
				$query .= " ".$index[$i]." ";
				$i_index--;
				if($i_index>0){
					$query .= ", ";
				}
			}
			$query .= ")";
		}
		
		$query .= ")";
		$this->get_query($query);
	}

	/**
	 * Agrega una columna al final de la tabla
	 */
	function add_column($table_name, $field_props){
		$name = $field_props[0];
		$type = $field_props[1];
		$is_not_null = ($field_props[2] == ConnectionDB::NOT_NULL);
		$key =  $field_props[3];
		
		$query = "ALTER TABLE ".$table_name." ADD ".$name." ".$type." ";
		
		if($is_not_null){
			$query .= ConnectionDB::NOT_NULL;
		}
		
		if($key == ConnectionDB::INDEX_KEY){
			$query .= " ADD INDEX (".$name." )";
		}
		
		$this->get_query($query);
	}

	//----------------------------------------------------------------------------
	//Funciones para las consultas
	//----------------------------------------------------------------------------

	/**
	 * Realiza una consulta y retorna el resultado
	 */
	public function get_query($query){
		$this->connect_DB();
		
		$result = mysql_query($query) or die('La consulta ha fallado: ' . mysql_error(). ' Consulta: '.$query);
		
		$values = array();
		while ($line = @mysql_fetch_array($result, MYSQL_NUM)) {
			$row = array();
			foreach($line as $i => $value){
				$row[] = $line[$i];
			}
			$values[] = $row;
		}
		
		@mysql_free_result($result);
        $this->disconnect_DB();
		return $values;
	}

	//----------------------------------------------------------------------------
	//Funciones de conexión con la base de datos
	//----------------------------------------------------------------------------

	/**
	 * Conecta a la DB
	 */
	private function connect_DB(){
		$this->link = mysql_connect(ConnectionDB::SERVER, $this->user_name, $this->user_pass) or die('No se pudo conectar con la BD: ' . mysql_error());
		mysql_select_db(ConnectionDB::DATABASE_NAME) or die('No existe esa DB: '.ConnectionDB::DATABASE_NAME);
	}

	/**
	 * Desconecta de la DB
	 */
	private function disconnect_DB(){
		mysql_close($this->link);
	}
}
?>